package lk.icta.core.dao.impl;

import lk.icta.common.dao.impl.BaseDAO;
import lk.icta.core.dao.LandApplicationDAO;
import lk.icta.core.entity.LandApplication;
import lk.icta.core.entity.WorkflowTask;
import lk.icta.user.entity.User;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

import javax.persistence.Query;
import java.util.ArrayList;
import java.util.Date;
import java.util.EnumSet;
import java.util.List;

/**
 * @author Tharanga Punchhihewa
 */
public class LandApplicationDAOImpl extends BaseDAO implements LandApplicationDAO {

    @Transactional(propagation = Propagation.MANDATORY)
    public void addLandApplication(LandApplication landApplication, User user) {
        landApplication.getLifeCycleInfo().setCreatedTimeStamp(new Date());
        landApplication.getLifeCycleInfo().setCreatedUser(user);
        landApplication.getLifeCycleInfo().setLastUpdatedTimeStamp(new Date());
        landApplication.getLifeCycleInfo().setLastUpdatedUser(user);
        em.persist(landApplication);
        logger.debug("Added Land application of land application id : {}", landApplication.getId());
    }

    @Transactional(propagation = Propagation.MANDATORY)
    public void updateLandApplication(LandApplication landApplication, User user) {
        logger.debug("Updating a Land Application");
        //todo: remove this
        landApplication.getLifeCycleInfo().setLastUpdatedTimeStamp(new Date());
        landApplication.getLifeCycleInfo().setLastUpdatedUser(user);
        em.merge(landApplication);
    }

    @Transactional(propagation = Propagation.SUPPORTS, readOnly = true)
    public LandApplication getLandApplicationByIdUKey(long id, User user) {
        logger.debug("Loading Land Application by application id {}", id);
        return em.find(LandApplication.class, id);
    }

    @Transactional(propagation = Propagation.NEVER, readOnly = true)
    public List<LandApplication> getApplicationsByState(User user, WorkflowTask state) {
        Query q = em.createNamedQuery("getApplicationsByState");
        q.setParameter("state", state);
        populateDsDivisionList(q, user);
        return q.getResultList();
    }

    @Transactional(propagation = Propagation.NEVER, readOnly = true)
    public List<LandApplication> getApplicationsByWorkFlowItemId(User user, int itemId) {
        Query q = em.createNamedQuery("getApplicationsByWorkFlowItemId");
        q.setParameter("id", itemId);
        populateDsDivisionList(q, user);
        return q.getResultList();
    }

    @Transactional(propagation = Propagation.NEVER, readOnly = true)
    public List<LandApplication> getInitialApplicationsByState(User user, LandApplication.State state) {
        Query q = em.createNamedQuery("getInitialApplicationsByState");
        q.setParameter("state", state);
        populateDsDivisionList(q, user);
        return q.getResultList();
    }

    @Transactional(propagation = Propagation.NEVER, readOnly = true)
    public List<LandApplication> getApplicationsByWorkItemList(User user, List<Integer> workItemList) {
        Query q = em.createNamedQuery("getApplicationsByWorkItemList");
        q.setParameter("workItemList", workItemList);
        populateDsDivisionList(q, user);
        return q.getResultList();
    }

    @Transactional(propagation = Propagation.NEVER, readOnly = true)
    public List getPendingApplicationsCountByWorkflowTask(User user, List<Integer> workItemList) {
        if (workItemList != null && !workItemList.isEmpty()) {
            Query q = em.createNamedQuery("getApplicationsByWorkItemList");
            q.setParameter("workItemList", workItemList);
            populateDsDivisionList(q, user);
            return q.getResultList();
        } else {
            return null;
        }
    }

    @Transactional(propagation = Propagation.NEVER, readOnly = true)
    public List<LandApplication> getPendingApplicationsByWorkflowTask(User user, List<Integer> workItemList) {
        Query q = em.createNamedQuery("getApplicationListByWorkItemList");
        q.setParameter("workItemList", workItemList);
        populateDsDivisionList(q, user);
        return q.getResultList();
    }

    @Override
    public List<LandApplication> getPendingApplicationsByWorkflowTaskByAlienationMethod(User user, List<Integer> workItemList, int alienationMethodId) {
        Query q = em.createNamedQuery("getApplicationListByWorkItemListByAlienationMethodId");
        q.setParameter("workItemList", workItemList);
        q.setParameter("alienationMethodId", alienationMethodId + "");
        populateDsDivisionList(q, user);
        return q.getResultList();
    }

    @Transactional(propagation = Propagation.NEVER, readOnly = true)
    public List<LandApplication> getApplicationsByStates(User user, EnumSet<WorkflowTask> stateSet) {
        Query q = em.createNamedQuery("getApplicationsByStates");
        q.setParameter("stateSet", stateSet);
        populateDsDivisionList(q, user);
        return q.getResultList();
    }

    @Transactional(propagation = Propagation.NEVER, readOnly = true)
    public List<LandApplication> getApplicationsByStatesGn(User user, int gnId, EnumSet<WorkflowTask> stateSet) {
        Query q = em.createNamedQuery("getApplicationsByStatesGn");
        q.setParameter("stateSet", stateSet);
        q.setParameter("gnId", gnId);
        populateDsDivisionList(q, user);
        return q.getResultList();
    }

    @Transactional(propagation = Propagation.NEVER, readOnly = true)
    public List<LandApplication> getApplicationsByStateAndAM(User user, LandApplication.State state, int alienationType) {
        Query q = em.createNamedQuery("getApplicationsByStateAndAM");
        q.setParameter("state", state);
        q.setParameter("alienationType", alienationType);
        q.setParameter("DSList", user.getDsDivisions());
        return q.getResultList();
    }

    @Transactional(propagation = Propagation.NEVER, readOnly = true)
    public List<LandApplication> getShortListedCandidates(EnumSet<WorkflowTask> stateSet, int alienationMethod) {
        Query q = null;
        if (alienationMethod == 0) {
            q = em.createNamedQuery("getAllApplicationsByWorkflowState");
            q.setParameter("stateSet", stateSet);
        } else if (alienationMethod == 1 || alienationMethod == 3 || alienationMethod == 4) {
            q = em.createNamedQuery("getAllApplicationsByWorkflowStateByAMS");
            q.setParameter("stateSet", stateSet);
            q.setParameter("alienationType", alienationMethod);
        } else {
            q = em.createNamedQuery("getAllApplicationsByWorkflowStateByAM");
            q.setParameter("stateSet", stateSet);
            q.setParameter("alienationType", alienationMethod);
        }
        return q.getResultList();
    }

    @Transactional(propagation = Propagation.NEVER, readOnly = true)
    public List<LandApplication> getApplicationsByStateAndRole(User user, WorkflowTask state) {
        Query q = em.createNamedQuery("getApplicationsByStateAndRole");
        q.setParameter("state", state);
        q.setParameter("role", user.getRole().getId());

        populateDsDivisionList(q, user);

        return q.getResultList();
    }

    @Transactional(propagation = Propagation.NEVER, readOnly = true)
    public List<LandApplication> getApplicationsByWorkflowTask(User user, WorkflowTask state) {
        Query q = em.createNamedQuery("getApplicationsByWorkflowTask");
        q.setParameter("task", state);
        populateDsDivisionList(q, user);

        return q.getResultList();
    }

    @Transactional(propagation = Propagation.NEVER, readOnly = true)
    public List<LandApplication> getRankedApplicationsByState(User user, LandApplication.State state) {
        Query q = em.createNamedQuery("getRankedApplicationsByState");
        q.setParameter("state", state);
        q.setParameter("DSList", user.getDsDivisions());
        return q.getResultList();
    }

    @Transactional(propagation = Propagation.NEVER, readOnly = true)
    public List<LandApplication> getApplicationsByWorkflowState(User user, WorkflowTask workflowTask) {
        Query q = em.createNamedQuery("getApplicationsByWorkflowState");
        q.setParameter("workflowState", workflowTask);
        q.setParameter("DSList", user.getDsDivisions());
        return q.getResultList();
    }

    @Transactional(propagation = Propagation.NEVER, readOnly = true)
    public List<LandApplication> getLandApplicationListByAlienationType(int alienationType, User user) {
        Query q = em.createNamedQuery("getApplicationsByAlienationType");
        q.setParameter("alienationType", alienationType);
        q.setParameter("DSList", user.getDsDivisions());
        return q.getResultList();
    }

    @Transactional(propagation = Propagation.NEVER, readOnly = true)
    public List<LandApplication> getSelectedLandApplicationListByAlienationType(int alienationType, User user) {
        Query q = em.createNamedQuery("getSelectedApplicationsByAlienationType");
        q.setParameter("alienationType", alienationType);
        q.setParameter("DSList", user.getDsDivisions());
        return q.getResultList();
    }

    @Transactional(propagation = Propagation.NEVER, readOnly = true)
    public List<LandApplication> getApplicationList(User user) {
        Query q = em.createNamedQuery("getApplicationsList");
        populateDsDivisionList(q, user);
        return q.getResultList();
    }

    @Transactional(propagation = Propagation.NEVER, readOnly = true)
    public List<LandApplication> searchApplicationsOfDSDivisionByPersonName(String personName, User user) {
        logger.debug("Search applications by person Name: {} of {}", personName);
        Query q = em.createNamedQuery("searchApplicationsOfDSDivisionByPersonName");
        q.setParameter("personName", "%" + personName + "%");
        populateDsDivisionList(q, user);

        return q.getResultList();
    }

    @Transactional(propagation = Propagation.NEVER, readOnly = true)
    public List<LandApplication> searchApplicationsOfDSDivisionByNIC(String nic, User user) {
        logger.debug("Search applications by NIC: {} of {}", nic);
        Query q = em.createNamedQuery("searchApplicationsOfDSDivisionByNIC");
        q.setParameter("nic", nic);
        populateDsDivisionList(q, user);

        return q.getResultList();
    }

    @Transactional(propagation = Propagation.NEVER, readOnly = true)
    public List<LandApplication> searchApplicationsByDsId(int dsDivisionId, User user) {
        logger.debug("Search applications by DSId: {} ", dsDivisionId);
        Query q = em.createNamedQuery("searchApplicationsByDsId");
        q.setParameter("DsId", dsDivisionId);
        return q.getResultList();
    }

    @Transactional(propagation = Propagation.NEVER, readOnly = true)
    public List<LandApplication> searchApplicationsByDistrictId(int districtId, User user) {
        logger.debug("Search applications by DS List by : {} ", user.getUsername());
        Query q = em.createNamedQuery("searchApplicationsByDistrictId");
        q.setParameter("districtId", districtId);

        return q.getResultList();
    }

    private void populateDsDivisionList(Query q, User user) {

        if (user != null && user.getDsDivisions() != null && user.getDsDivisions().size() != 0) {
            //for orlp and lcg, find all apps, ignore DS filter
            logger.debug("ds div list size : {}", user.getDsDivisions().size());
            q.setParameter("ignoreDS", 0);
            q.setParameter("DSList", user.getDsDivisions());

        } else {
            q.setParameter("ignoreDS", 1);
            q.setParameter("DSList", null);
        }

    }

    @Transactional(propagation = Propagation.NEVER, readOnly = true)
    public List<LandApplication> getApplicationsByStateSetAndUser(User user) {
        Query q = em.createNamedQuery("getApplicationsByStateAndUser");
        q.setParameter("state", LandApplication.State.INITIAL_APPLICATION_CLOSED);
        q.setParameter("user", user.getUsername());

        return q.getResultList();
    }

    @Transactional(propagation = Propagation.NEVER, readOnly = true)
    public List<LandApplication> getApplicationsByUser(User user) {
        Query q = em.createNamedQuery("getApplicationsByUser");
        q.setParameter("user", user.getUsername());

        return q.getResultList();
    }

    public List<LandApplication> getApplicationsByProvinceForSlcUsers(int provinceId) {
        Query q = em.createNamedQuery("getApplicationsByProvinceForSlcUsers");
        q.setParameter("provinceId", provinceId);
        return q.getResultList();
    }

    @Transactional(propagation = Propagation.NEVER, readOnly = true)
    public List<LandApplication> getApplicationsByStateAndDistrict(User user, WorkflowTask state, int districtId) {
        Query q = em.createNamedQuery("getApplicationsByStateAndDistrict");
        q.setParameter("state", state);
        q.setParameter("districtId", districtId);
        return q.getResultList();
    }

    @Transactional(propagation = Propagation.NEVER, readOnly = true)
    public List<LandApplication> getApplicationsByStateAndDsId(WorkflowTask state, int dsDivisionId, User user) {
        Query q = em.createNamedQuery("getApplicationsByStateAndDsDivisionId");
        q.setParameter("state", state);
        q.setParameter("dsDivisionId", dsDivisionId);
        return q.getResultList();
    }

    @Transactional(propagation = Propagation.NEVER, readOnly = true)
    public List<LandApplication> searchAlienationsByPersonName(String holderName, User user, WorkflowTask state) {
        logger.debug("Search alienations by person Name: {} of {}", holderName, user);
        logger.debug(" {}", state);
        Query q = em.createNamedQuery("searchApplicationsByStateByPersonName");
        q.setParameter("personName", "%" + holderName + "%");
        q.setParameter("state", state);
        populateDsDivisionList(q, user);
        return q.getResultList();
    }

    @Transactional(propagation = Propagation.NEVER, readOnly = true)
    public List<LandApplication> searchAlienationsByPersoNIC(String holderNIC, User user, WorkflowTask state) {
        logger.debug("Search alienations by person NIC: {} of {}", holderNIC, user);
        Query q = em.createNamedQuery("searchApplicationsByStateByNIC");
        q.setParameter("NIC", holderNIC);
        q.setParameter("state", state);
        populateDsDivisionList(q, user);
        return q.getResultList();
    }

    @Transactional(propagation = Propagation.NEVER, readOnly = true)
    public List<LandApplication> searchAlienationsByAlienationSerialNumber(long alienationSerialNumber, User user, WorkflowTask state) {
        logger.debug("Search alienations by Alienation Serial Number: {} of {}", alienationSerialNumber, user);
        Query q = em.createNamedQuery("searchAlienationByAlienationSerialNumber");
        q.setParameter("ASL", alienationSerialNumber);
        q.setParameter("state", state);
        populateDsDivisionList(q, user);
        return q.getResultList();
    }

    @Transactional(propagation = Propagation.NEVER, readOnly = true)
    public List<LandApplication> searchAlienationsByLnadSerialNumber(long landSerialNumber, User user, WorkflowTask state) {
        logger.debug("Search alienations by Land Serial Number {} of {}", landSerialNumber, user);
        Query q = em.createNamedQuery("searchAlienationByLandSerialNumber");
        q.setParameter("serialNumber", landSerialNumber);
        q.setParameter("state", state);
        populateDsDivisionList(q, user);
        return q.getResultList();
    }

    @Transactional(propagation = Propagation.NEVER, readOnly = true)
    public List<LandApplication> searchAlienationByAlienationReferenceNumber(String alienationNumber, User user, WorkflowTask state) {
        logger.debug("Search alienations by Alienation Number {} of {}", alienationNumber, user);
        Query q = em.createNamedQuery("searchAlienationByReferenceNumber");
        q.setParameter("alienationNumber", alienationNumber);
        q.setParameter("state", state);
        populateDsDivisionList(q, user);
        return q.getResultList();
    }

    @Transactional(propagation = Propagation.NEVER, readOnly = true)
    public List<LandApplication> searchAlienationsByAlienationMethodId(int alienationMethodId, User user, WorkflowTask state) {
        logger.debug("Search alienations by Alienation Method {} of {}", alienationMethodId, user);
        Query q = em.createNamedQuery("searchAlienationByMethod");
        q.setParameter("alienationMethodId", alienationMethodId);
        q.setParameter("state", state);
        populateDsDivisionList(q, user);
        return q.getResultList();
    }

    @Transactional(propagation = Propagation.NEVER, readOnly = true)
    public List<LandApplication> searchAllAlienationsByAlienationMethodId(User user, WorkflowTask state) {
        logger.debug("Search alienations by All Alienation Methods of {}", user);
        Query q = em.createNamedQuery("searchAllAlienationByMethod");
        q.setParameter("state", state);
        populateDsDivisionList(q, user);
        return q.getResultList();
    }

    @Override
    public List<Object[]> getLandApplicationStatistics(int id, String searchBy, int initAppState, int wftState, Date fromDate, Date toDate, String language) {
        List<Object[]> result = new ArrayList<Object[]>();
        if (initAppState == 1) {
            result = getInitStateApplications(id, searchBy, fromDate, toDate, initAppState, language);
        } else if (wftState > 1) {
            result = getWftStateApplications(id, searchBy, fromDate, toDate, wftState, language);
        } else {
            initAppState = 1;
            result.addAll(getInitStateApplications(id, searchBy, fromDate, toDate, initAppState, language));
            result.addAll(getWftStateApplications(id, searchBy, fromDate, toDate, wftState, language));
        }

        return result;
    }

    private List<Object[]> getWftStateApplications(int id, String searchBy, Date fromDate, Date toDate, int wftState, String language) {
        String query = "SELECT l.workflowItem.workflowTask AS wftState, COUNT(l) AS count FROM LandApplication l";
        if (wftState > 1 || searchBy != null || (fromDate != null && toDate != null)) {
            boolean isAdded = false;
            query += " WHERE ";
            if (searchBy != null) {
                query += searchBy;
                isAdded = true;
            }

            if ((fromDate != null && toDate != null)) {
                query += isAdded ? " AND " : "";
                query += "l.lifeCycleInfo.lastUpdatedTimeStamp BETWEEN :start AND :end";
                isAdded = true;
            }

            if (wftState > 1) {
                query += isAdded ? " AND " : "";
                //query += "l.workflowItem.workflowTask = :wftState";

                logger.debug("\n\n\n\n wfSt {} ", wftState);
                if (wftState == 2) {
                    query += "l.workflowItem.workflowTask in (0,1,2)";
                } else if (wftState == 3) {
                    query += "l.workflowItem.workflowTask in (3,4)";
                } else if (wftState == 4) {
                    query += "l.workflowItem.workflowTask in (5,6,7)";
                } else if (wftState == 5) {
                    query += "l.workflowItem.workflowTask in (8)";
                } else if (wftState == 6) {
                    query += "l.workflowItem.workflowTask in (9,10,11)";
                } /*else if (wftState == 7) {
                    query += "l.workflowItem.workflowTask in (12,13,14)";
                }*/ else if (wftState == 7) {
                    query += "l.workflowItem.workflowTask in (15,16,17,18,19,20," +
                            "110,111,112,113," +
                            "401,411,421,431," +
                            "501,511,521,531," +
                            "601,611,621,631," +
                            "701,711,721,731," +
                            "811,821,831,841," +
                            "911,921,931,941)";   //ords
                } else if (wftState == 8) {
                    query += "l.workflowItem in (31,32," +
                            "100,101,102,103,104,105,106," +
                            "41,42,402,412,422,432,442,452,462," +
                            "51,52,502,512,522,532,542,552,562," +
                            "61,62,602,612,622,632,642,652,662," +
                            "71,72,702,712,722,732,742,752,762," +
                            "81,82," +
                            "91,92)";    //plc
                } else if (wftState == 9) {
                    query += "l.workflowItem in (33,34," +
                            "43,44," +
                            "53,54," +
                            "63,64," +
                            "73,74)";     //lcg
                } else if (wftState == 10) {
                    query += "l.workflowItem in (35,36,37,65,66,75,76)";      //orlp
                } else if (wftState == 11) {
                    query += "l.workflowItem in (22,23,24)";      //prepare AD
                } else if (wftState == 12) {
                    query += "l.workflowItem in (25)";      //issue AD
                }


                isAdded = true;
            }

        }
        query += " GROUP BY l.workflowItem.workflowTask";

        logger.debug("Dynamic Statistics Query  : {}", query);
        Query q = em.createQuery(query);

        if (searchBy != null) q.setParameter("divisionId", id);
        if ((fromDate != null && toDate != null)) {
            q.setParameter("start", fromDate);
            q.setParameter("end", toDate);
        }
        //if(wftState != null) q.setParameter("wftState", wftState);
        List<Object[]> result = q.getResultList();

        for(Object o[] : result){
            WorkflowTask val1 = (WorkflowTask) o[0];
            if("en".equals(language)){
                o[0] = val1.getAction();
            }else if("si".equals(language)){
                o[0] = val1.getActionInSi();
            }else if("ta".equals(language)){
                o[0] = val1.getActionInTa();
            }
        }
        return result;
    }

    private List<Object[]> getInitStateApplications(int id, String searchBy, Date fromDate, Date toDate, int initAppState, String language) {
        String query = "SELECT l.initialStatus AS initState, COUNT(l) AS count FROM LandApplication l";
        if (initAppState == 1 || searchBy != null || (fromDate != null && toDate != null)) {
            boolean isAdded = false;
            query += " WHERE ";
            if (searchBy != null) {
                query += searchBy;
                isAdded = true;
            }

            if ((fromDate != null && toDate != null)) {
                query += isAdded ? " AND " : "";
                query += "l.lifeCycleInfo.lastUpdatedTimeStamp BETWEEN :start AND :end";
                isAdded = true;
            }

            //if (initAppState == 1) {
            query += isAdded ? " AND " : "";
            // query += "l.initialStatus = :initAppState";
            query += "l.initialStatus in (0,1,3)";
            isAdded = true;
            //}

        }
        query += " GROUP BY l.initialStatus";

        logger.debug("Dynamic Statistics Query  : {}", query);
        Query q = em.createQuery(query);

        if (searchBy != null) q.setParameter("divisionId", id);
        if ((fromDate != null && toDate != null)) {
            q.setParameter("start", fromDate);
            q.setParameter("end", toDate);
        }
        //if(initAppState != null) q.setParameter("initAppState", initAppState);
        List<Object[]> result = q.getResultList();

        for(Object o[] : result){
            LandApplication.State val1 = ( LandApplication.State) o[0];

            if("en".equals(language)){
                o[0] = val1.getNameInEn();
            }else if("si".equals(language)){
                o[0] = val1.getNameInSi();
            }else if("ta".equals(language)){
                o[0] = val1.getNameInTa();
            }
        }
        return result;
    }
}
